package com.example.demo.util.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *Description:
 * @Author zhengmf
 * @Date 2018/4/5  15:18
 * 读取Excel
 */
public class ExcelReadUtil {

    public static final String EXCEL_2003_POSTFIX = "xls";
    public static final String EXCEL_2010_POSTFIX = "xlsx";
    public static final String EMPTY = "";
    public static final String POINT = ".";

    private int totalRows; //sheet中总行数
    private static int totalCells; //表头总单元格数

    /**
     * read the Excel .xlsx,.xls
     * @param file 上传的文件
     */
    public List<ArrayList<String>> readExcel(MultipartFile file) {
        if(file == null || EMPTY.equals(file.getOriginalFilename().trim())){
            return null;
        } else {
            String postfix = "";
            if(file.getOriginalFilename().contains(POINT)){
                postfix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(POINT)+1);
            }
            if(!EMPTY.equals(postfix)){
                if(EXCEL_2003_POSTFIX.equals(postfix)){
                    return readXls(file);
                }else if(EXCEL_2010_POSTFIX.equals(postfix)){
                    return readXlsx(file);
                }else{
                    return null;
                }
            }
        }
        return null;
    }

    /**
     * read the Excel 2010 .xlsx
     * @param file
     */
    private List<ArrayList<String>> readXlsx(MultipartFile file){
        List<ArrayList<String>> list = new ArrayList<>();
        // IO流读取文件
        InputStream input = null;
        ArrayList<String> rowList;
        try {
            input = file.getInputStream();
            // 创建文档
            XSSFWorkbook wb = new XSSFWorkbook(input);
            //读取sheet(页)
            for(int numSheet=0;numSheet< wb.getNumberOfSheets();numSheet++){
                XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
                if(xssfSheet == null){
                    continue;
                }
                totalRows = xssfSheet.getLastRowNum() + 1;
                //表头几列，以下就按几列循环，没数据的为""
                totalCells = xssfSheet.getRow(0).getLastCellNum();
                //读取Row,从第yi行开始,表头有可能被用户删掉，记录下上传文件的表头
                for(int rowNum = 0;rowNum < totalRows;rowNum++){
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if(xssfRow != null){
                        rowList = new ArrayList<>();
                        //读取列，从第一列开始
                        for(int c = 0;c < totalCells ; c++){
                            XSSFCell cell = xssfRow.getCell(c);
                            if(cell == null){
                                rowList.add(EMPTY);
                                continue;
                            }
                            rowList.add(getXSSValue(cell).trim());
                        }
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                if (input != null) {
                    input.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;

    }
    /**
     * read the Excel 2003-2007 .xls
     */
    private List<ArrayList<String>> readXls(MultipartFile file){
        List<ArrayList<String>> list = new ArrayList<>();
        // IO流读取文件
        InputStream input = null;
        ArrayList<String> rowList;
        try {
            input = file.getInputStream();
            // 创建文档
            HSSFWorkbook wb = new HSSFWorkbook(input);
            //读取sheet(页)
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
                HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
                if(hssfSheet == null){
                    continue;
                }
                //表头几列，以下就按几列循环，没数据的为""
                totalCells = hssfSheet.getRow(0).getLastCellNum();
                totalRows = hssfSheet.getLastRowNum() + 1;
                //读取Row,从第yi行开始,表头有可能被用户删掉，记录下上传文件的表头
                for(int rowNum = 0;rowNum <= totalRows;rowNum++){
                    //表头几列，以下就按几列循环，没数据的为""
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if(hssfRow != null){
                        rowList = new ArrayList<>();
                        //读取列，从第一列开始
                        for(int c = 0;c < totalCells;c++){
                            HSSFCell cell = hssfRow.getCell(c);
                            if(cell==null){
                                rowList.add(EMPTY);
                                continue;
                            }
                            rowList.add(getHSSValue(cell).trim());
                        }
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                if (input != null) {
                    input.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }


    /**
     *
     * @param xssfCell
     * @return
     */
    public String getXSSValue(XSSFCell xssfCell){
        if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            String cellValue;
            if(DateUtil.isCellDateFormatted(xssfCell)){
                SimpleDateFormat sdf =   new SimpleDateFormat("yyyy/MM/dd");
                Date date = DateUtil.getJavaDate(xssfCell.getNumericCellValue());
                cellValue = sdf.format(date);
            }else{
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(xssfCell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1);
                if(strArr.equals("00")){
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

    /**
     *
     * @param hssfCell
     * @return
     */
    public String getHSSValue(HSSFCell hssfCell){
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            String cellValue;
            if(HSSFDateUtil.isCellDateFormatted(hssfCell)){
                SimpleDateFormat sdf =   new SimpleDateFormat("yyyy/MM/dd");
                Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());
                cellValue = sdf.format(date);
            }else{
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(hssfCell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1);
                if(strArr.equals("00")){
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }


}
